# 第二版
import wx
import pandas as pd
from itertools import combinations_with_replacement,combinations
import threading
from math import comb as mathcpmb

class MyFrame(wx.Frame):
    def __init__(self, parent, title):
        super(MyFrame, self).__init__(parent, title=title, size=(800, 480))
        
        self.SetSize(800, 480)
        self.Center()

        panel = wx.Panel(self)
        vbox = wx.BoxSizer(wx.HORIZONTAL)
        
        # 创建文件选择按钮和文本框
        self.file_a_picker = wx.FilePickerCtrl(panel, message="选择文件A", wildcard="CSV files (*.csv)|*.csv")
        self.file_b_picker = wx.FilePickerCtrl(panel, message="选择文件B", wildcard="XLS files (*.xls)|*.xls")
        self.file_c_picker = wx.FilePickerCtrl(panel, message="选择文件C", wildcard="XLS files (*.xls)|*.xls")
        self.output_text = wx.TextCtrl(panel, value="output")
        # 创建立即执行按钮
        self.execute_button = wx.Button(panel, label="立即执行")
        self.execute_button.Disable()  # 初始状态下禁用执行按钮
        # 创建立即检索按钮
        self.search_button = wx.Button(panel, label="开始检索")
        self.search_button.Disable()  # 初始状态下禁用执行按钮
        # 创建文本显示框
        self.result_text = wx.TextCtrl(panel, style=wx.TE_MULTILINE|wx.TE_READONLY)
        self.maybe_text = wx.TextCtrl(panel, style=wx.TE_MULTILINE|wx.TE_READONLY)
        self.read_label = wx.StaticText(panel, label='准备就绪')
        self.gauge = wx.Gauge(panel, range=100)


        # 绑定事件处理函数
        self.file_a_picker.Bind(wx.EVT_FILEPICKER_CHANGED, self.on_file_a_selected)
        self.file_b_picker.Bind(wx.EVT_FILEPICKER_CHANGED, self.on_file_b_selected)
        self.file_c_picker.Bind(wx.EVT_FILEPICKER_CHANGED, self.on_file_c_selected)
        self.execute_button.Bind(wx.EVT_BUTTON, self.on_execute)
        self.search_button.Bind(wx.EVT_BUTTON, self.on_search)
        

        hbox1 = wx.BoxSizer(wx.VERTICAL)

        hbox1.Add(wx.StaticText(panel, label='选择文件A:历史明细'), flag=wx.ALL, border=5)
        hbox1.Add(self.file_a_picker, proportion=1, flag=wx.EXPAND | wx.ALL, border=5)
        hbox1.Add(wx.StaticText(panel, label='选择文件B:原始数据信息'), flag=wx.ALL, border=5)
        hbox1.Add(self.file_b_picker, proportion=1, flag=wx.EXPAND | wx.ALL, border=5)
        hbox1.Add(wx.StaticText(panel, label='选择文件C:接口生成查询列表'), flag=wx.ALL, border=5)
        hbox1.Add(self.file_c_picker, proportion=1, flag=wx.EXPAND | wx.ALL, border=5)
        hbox1.Add(wx.StaticText(panel, label='自定义输出文件名'), flag=wx.ALL, border=5)
        hbox1.Add(self.output_text, proportion=1, flag=wx.EXPAND | wx.ALL, border=5)
        hbox1.Add(self.execute_button, proportion=1, flag=wx.EXPAND | wx.ALL, border=5)
        hbox1.Add(self.search_button, proportion=1, flag=wx.EXPAND | wx.ALL, border=5)


        hbox2 = wx.BoxSizer(wx.VERTICAL)
        hbox2.Add(wx.StaticText(panel, label='执行结果显示框'), flag=wx.ALL, border=5)
        hbox2.Add(self.result_text, proportion=1, flag=wx.EXPAND | wx.ALL, border=5)
        hbox2.Add(wx.StaticText(panel, label='检索结果显示框'), flag=wx.ALL, border=5)
        hbox2.Add(self.maybe_text, proportion=3, flag=wx.EXPAND | wx.ALL, border=5)


        hbox21 = wx.BoxSizer(wx.HORIZONTAL)
        hbox21.Add(self.read_label, proportion=1, flag=wx.EXPAND | wx.ALL, border=5)
        hbox21.Add(self.gauge, proportion=1, flag=wx.EXPAND | wx.ALL, border=5)
        hbox2.Add(hbox21, proportion=0.1, flag=wx.EXPAND | wx.ALL, border=5)

        vbox.Add(hbox1, proportion=1, flag=wx.EXPAND | wx.ALL, border=5)
        vbox.Add(hbox2, proportion=2, flag=wx.EXPAND | wx.ALL, border=5)

        panel.SetSizer(vbox)

    def on_file_a_selected(self, event):
        self.a_path = self.file_a_picker.GetPath()
        self.check_execute_button_status()
        
    def on_file_b_selected(self, event):
        self.b_path = self.file_b_picker.GetPath()
        self.check_execute_button_status()
        
    def on_file_c_selected(self, event):
        self.c_path = self.file_c_picker.GetPath()
        self.check_execute_button_status()
        
    def on_execute(self, event):
        output_filename = self.output_text.GetValue() #+ ".csv"
        result_data,total,keys = findData(self.a_path, self.b_path, self.c_path, output_filename)
        self.total = total
        self.error_values = keys
        self.result_text.SetValue(result_data)
        wx.MessageBox("操作完成！ 接下来可以尝试开始检索。", "提示")
        self.execute_button.Disable()
        self.search_button.Enable()

    def on_search(self, event):
        wx.MessageBox("本操作耗时可能过长，如需结束运行，请直接关闭本程序", "警告")
        t = threading.Thread(target=self.do_search)
        self.search_button.Disable()
        t.start()
        # wx.CallLater(500,self.do_search)

    def do_search(self):
        values = self.error_values
        target = self.total
        num=1
        value_len = int((len(values) + 1) / 2)
        for i in range(1, value_len):
            # allcount = mathcpmb(len(values) + i - 1, i)
            allcount = mathcpmb(len(values), i)
            self.read_label.SetLabel(f'寻找{i}个数组成的{allcount}种可能\n')
            self.gauge.SetRange(allcount)
            count = 1
            # for comb in combinations_with_replacement(values, i):
            for comb in combinations(values, i):
                wx.CallAfter(self.gauge.SetValue, count) # 更新进度条
                if round(sum(comb), 2) == target:
                    self.maybe_text.AppendText(f'组合{num}: {list(comb)}\n')# + ', '.join(list(comb))
                    num+=1
                    print(list(comb))   
                count+=1  

            allcount = mathcpmb(len(values), len(values) + 1 - i)
            self.read_label.SetLabel(f'寻找{len(values) + 1 - i}个数组成的{allcount}种可能\n')
            self.gauge.SetRange(allcount)
            count = 1
            for comb in combinations(values, len(values) + 1 - i):
                wx.CallAfter(self.gauge.SetValue, count) # 更新进度条
                if round(sum(comb), 2) == target:
                    self.maybe_text.AppendText(f'组合{num}: {list(comb)}\n')# + ', '.join(list(comb))
                    num+=1
                    print(list(comb))   
                count+=1  


    def check_execute_button_status(self):
        if hasattr(self, 'a_path') and hasattr(self, 'b_path') and hasattr(self, 'c_path'):
            self.execute_button.Enable()
        else:
            self.execute_button.Disable()

def findData(a_path, b_path, c_path, output_filename):
    # 读取B表数据
    b_data = pd.read_excel(b_path)
    b_column = '金额'

    # 将"金额"列转换为浮点数类型
    b_data[b_column] = pd.to_numeric(b_data[b_column].str.replace(',',''), errors='coerce')

    # 计算B表汇总
    b_total = b_data[b_column].sum()

    # 统计B表中不同金额的出现频次
    b_counts = b_data[b_column].value_counts().sort_index(ascending=False).to_dict()

    # 读取A表数据
    a_data = pd.read_csv(a_path, skiprows=4)
    a_column = '入款'

    # 过滤A表数据，选择出款为0且摘要不为'划款'的行
    a_filtered_data = a_data[(a_data['出款'] == '0') & (a_data['摘要'] != '划款')]

    # 将"金额"列转换为浮点数类型
    a_filtered_data[a_column] = pd.to_numeric(a_filtered_data[a_column].str.replace(',',''), errors='coerce')

    # 计算A表汇总
    a_total = a_filtered_data[a_column].sum()
    a_total = round(a_total,2)

    # 统计A表中不同金额的出现频次
    a_counts = a_filtered_data[a_column].value_counts().sort_index(ascending=False).to_dict()

    # 读取C表数据
    c_data = pd.read_excel(c_path)
    c_column = '实际支付金额'

    # 将"实际支付金额"列转换为浮点数类型
    c_data[c_column] = pd.to_numeric(c_data[c_column], errors='coerce')

    # 计算C表汇总
    c_total = c_data[c_column].sum()

    # 统计C表中不同金额的出现频次
    c_counts = c_data[c_column].value_counts().sort_index(ascending=False).to_dict()

    # 打印A表统计
    # print("A表统计:")
    print("A表汇总:", a_total)
    # a_data = json.dumps(a_counts, indent=4)
    # print("A表统计频次:", A_data)

    # 打印B表统计
    # print("B表统计:")
    print("B表汇总:", b_total)
    # b_data = json.dumps(b_counts, indent=4)
    # print("B表统计频次:", B_data)

    # 打印C表统计
    # print("C表统计:")
    print("C表汇总:", c_total)
    # c_data = json.dumps(c_counts, indent=4)

    # 创建DataFrame用于存储数据
    df = pd.DataFrame(columns=['key', 'A_value', 'B_value', 'C_value'])

    # 合并所有的键，并按照浮点数大小排序
    keys = sorted(set(a_counts.keys()) | set(b_counts.keys()) | set(c_counts.keys()), key=float)

    # 将数据添加到DataFrame中
    for key in keys:
        a_value = a_counts.get(key, 0)
        b_value = b_counts.get(key, 0)
        c_value = c_counts.get(key, 0)
        row = {'key': key, 'A_value': a_value, 'B_value': b_value, 'C_value': c_value}
        df = pd.concat([df, pd.DataFrame(row, index=[0])], ignore_index=True)

    # 创建Excel写入器
    writer = pd.ExcelWriter(f'{output_filename}.xlsx', engine='xlsxwriter')

    # 将DataFrame写入Excel
    df.to_excel(writer, index=False, sheet_name='Sheet1')

    # 获取Excel工作簿和工作表对象
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']

    # 设置黄色填充样式
    yellow_fill = workbook.add_format({'bg_color': 'yellow'})

    # 检查每行数据的条件并设置单元格样式
    keys = []
    for row in range(1, len(df) + 1):
        a_value = df.loc[row - 1, 'A_value']
        b_value = df.loc[row - 1, 'B_value']
        c_value = df.loc[row - 1, 'C_value']
        if a_value + b_value != c_value:
            worksheet.conditional_format(row, 0, row, 0, {'type': 'no_errors', 'format': yellow_fill})
            key_v = df.loc[row - 1, 'key']
            if key_v == 0.0:
                continue
            keys.append(key_v)

    print(keys)

    # 保存Excel文件
    writer._save()

    re = ''
    more = 0.00
    if a_total + b_total == c_total:
        re = '='
    elif a_total + b_total > c_total:
        re = '>'
        more = a_total + b_total - c_total
    else:
        re = '<'
        more =  c_total - a_total - b_total

    more = round(more,2)
    
    result = f'''A表汇总: {a_total}, B表汇总: {b_total}, C表汇总: {c_total}
{a_total} + {b_total} {re} {c_total}  差异为{more}
可能从以下金额中产生：{keys}'''
    
    return result,more,keys

if __name__ == '__main__':
    app = wx.App()
    frame = MyFrame(None, "数据分析工具2.0")
    frame.Show()
    app.MainLoop()
